package com.jse.jdbc;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.StringJoiner;

import javax.sql.DataSource;

import com.jse.Casts;
import com.jse.Io;
import com.jse.Ioc;
import com.jse.Jse;
import com.jse.Lang;
import com.jse.anno.Table;
import com.jse.json.Json;
import com.jse.json.JsonArray;
import com.jse.json.JsonObject;

public interface Dao {
	
	default DataSource dataSource() {return Ioc.get("dataSource");}
	default Connection getConnection(){return Jdbc.getConnection(dataSource());}
	
	default List<JsonObject> query(String table){
		return query(table,null);
	}
	default List<JsonObject> query(String table,Object where){
		return query(table, where,null);
	}
	default List<JsonObject> query(String table,Object where,Pager pager){
		if(!table.trim().toLowerCase().startsWith("select")) {
			table="select * from "+table;
		}
		if(where instanceof Cnd cnd) {
			if(pager!=null)cnd.pager(pager);
			return queryForList(table+cnd,cnd.getParams().toArray());
		}
		if(pager==null)return queryForList(table,where);
		return queryForList(table+pager.toSQL(),where);
	}
	default List<JsonObject> query(String table,Object where,int page,int limit)throws SQLException{
		return query(table, where, new Pager(page,limit));
	}
	default Pager table(String table,Object where,int page,int limit)throws SQLException{return pager(table,where,page,limit);}
	default Pager table(String table,Object where,Pager pager){return pager(table, where, pager);}
	default Pager pager(String table,Object where,Pager pager){
		var list=query(table, where,pager);
		pager.addData(list);
		if(pager.getSql()!=null) {
			table=pager.getSql();
		}else if(!table.trim().toLowerCase().startsWith("select")) {
			table="select count(*) from "+table;
		}else {
			table=table.replace(" * "," count(*) ");
		}
		if(where instanceof Cnd cnd) {
			pager.setTotal(count(table,cnd));
		}else
		pager.setTotal(count(table,where));
		return pager;
	}
	default Pager pager(String table,Object where,int page,int limit){
		return pager(table, where,new Pager(page,limit));
	}
	
	
	default Map<String,Object> fetch(String table,Object where){
		if(!table.trim().toLowerCase().startsWith("select")) {
			table="select * from "+table;
		}
		if(where instanceof Cnd cnd) {
			return queryForMap(table+where.toString(),cnd.getParams().toArray());
		}
		return queryForMap(table,where);
	}
	
	default Object select(String sql) throws SQLException {
		return queryForList(sql,Map.class);
	}
	default Object select(String sql,String type,Object...args) throws SQLException {
		if(type.equals("kv")) {
			var smap=new JsonObject();
			queryForList(sql,JsonObject.class, args).forEach(m->{smap.put(m.getString("key"),m.get("value"));});
			return smap;
		}else if(type.equals("dict")) {
			var smap=new HashMap<String,String>();
			queryForList(sql,JsonObject.class, args).forEach(m->{smap.put(m.getString("key"),m.getString("value"));});
			return smap;
		}else if(type.equals("list")) {return queryForList(sql,JsonObject.class, args);
		}else if(type.equals("list<int>")) {return queryForList(sql,Integer.class, args);
		}else if(type.equals("list<long>")) {return queryForList(sql,Long.class, args);
		}else if(type.equals("list<string>")) {return queryForList(sql,String.class, args);
		}else if(type.equals("set<int>")) {return Lang.ofSet(queryForList(sql,Integer.class, args));
		}else if(type.equals("set<long>")) {return Lang.ofSet(queryForList(sql,Long.class, args));
		}else if(type.equals("set<string>")) {return Lang.ofSet(queryForList(sql,String.class, args));
		}else if(type.equals("map")) {return queryForMap(sql,args);
		}else if(type.equals("int")) {return queryForObject(sql,Integer.class,args);
		}else if(type.equals("long")) {return queryForObject(sql,Long.class,args);
		}else if(type.equals("string")) {return queryForObject(sql,String.class,args);
		}
		return queryForList(sql,JsonObject.class,args);
	}
	
	default List<JsonObject> queryForList(String sql,Object...args){
		return queryForList(JsonObject.class, sql, args);
	}
	default <T> List<T> queryForList(String sql,Class<T> cls,Object...args){
		return queryForList(cls,sql,args);
	}
	default <T> List<T> queryForList(Class<T> cls,String sql,Object...args){
		try(var conn=getConnection();var stmt=conn.prepareStatement(sql);){
			if(sql.indexOf('?')!=-1)Jdbc.setParam(stmt,args);
			if(Jse.showsql&&stmt.getClass().getSimpleName().equals("ClientPreparedStatement")) {
				Jdbc.log.debug("queryForList%s",stmt.toString().substring(stmt.getClass().getName().length()));
			}
			var rs=stmt.executeQuery();
			var list=new JsonArray();
			var md=rs.getMetaData();
			int count=md.getColumnCount();
			String[] headers = new String[count];
	        for (int i = 1; i <= count; i++)
	            headers[i - 1] = md.getColumnLabel(i);
			while (rs.next()) {
				 if(Map.class.isAssignableFrom(cls))list.add(new JsonObject(rs,md,headers));
				 else list.add(rs.getObject(1));//基础类型 string
			}
			Io.close(rs);
			return (List<T>)list;
		}catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		}catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}
	
	default JsonObject queryForMap(String sql,Object...args) {
		try(var conn=getConnection();var stmt=conn.prepareStatement(sql);){
			if(sql.indexOf('?')!=-1)Jdbc.setParam(stmt,args);
			if(Jse.showsql&&stmt.getClass().getSimpleName().equals("ClientPreparedStatement")) {
				Jdbc.log.debug("queryForMap%s",stmt.toString().substring(stmt.getClass().getName().length()));
			}
			var rs=stmt.executeQuery();
			JsonObject json=null;
			if(rs.next())json=new JsonObject(rs);
			Io.close(rs);
			return json;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	default <T> T queryForObject(String sql,Class<T> cls,Object...args){return queryForObject(cls, sql, args);}
	default <T> T queryForObject(Class<T> cls,String sql,Object...args){
		try(var conn=getConnection();var stmt=conn.prepareStatement(sql);){
			if(sql.indexOf('?')!=-1)Jdbc.setParam(stmt,args);
			if(Jse.showsql&&stmt.getClass().getSimpleName().equals("ClientPreparedStatement")) {
				Jdbc.log.debug("queryForObject%s",stmt.toString().substring(stmt.getClass().getName().length()));
			}
			var rs=stmt.executeQuery();
			Object o=null;
			if(cls.getName().startsWith("java")){
				if(rs.next()){o=Casts.to(rs.getObject(1),cls);}
			}else {
				o=new JsonObject(rs).toBean(cls);
			} 
			Io.close(rs);
			return (T)o;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	default Object save(Object o){return insert(o);}
	default Object insert(Object o){
		if(o instanceof Collection c)return insert(c);
		if(o.getClass().getName().endsWith(".ScriptObjectMirror")) {
			if(o.toString().equals("[object Array]"))return insert(((Map)o).values());
		}
		var m=toMap(o);
		String table=m.remove("table").toString();
		Object id=m.remove("+id");//id name
		var column=new StringJoiner(",");
		var values=new StringJoiner(",");
		m.forEach((k,v)->{
			column.add("`"+k+"`");values.add("?");
		});
		String sql="INSERT INTO %s (%s) VALUES (%s)".formatted(table,column,values);
		var conn=getConnection();
		try(var stmt=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS)){
			var args=m.values().toArray();
			Jdbc.setParam(stmt,args);
			if(Jse.showsql&&stmt.getClass().getSimpleName().equals("ClientPreparedStatement")) {
				Jdbc.log.debug("insert sql"+stmt.toString().substring(stmt.getClass().getName().length()));
			}
			var status=stmt.execute();
			if(status&&id!=null) {
				var rs = stmt.getGeneratedKeys();
				if (rs.next())m.put(id,rs.getInt(1));
				Io.close(rs);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}finally {
			Io.close(conn);
		}
		return m;
	}
	default Object insert(Collection<?> c){//批量插入
		return c;
	}
	default Object mager(Object o) {
		var m=toMap(o);
		var idName=m.get("+id");
		Object pk="id";
		if(idName!=null)pk=m.get(idName);
		if(Lang.isEmpty(m.get(pk))) {
			return insert(m);
		}else {
			return update(m);
		}
	}
	private Map toMap(Object o) {
		Map m=null;
		if(o instanceof Map m0)m=m0;
		else{
			m=Json.jsonObject(o);
			if(!m.containsKey("table")) {
				var tab=o.getClass().getAnnotation(Table.class);
				if(tab!=null) {
					m.put("table",tab.value().isEmpty()?o.getClass().getSimpleName():tab.value());
					if(tab.auto()) {
						m.put("+id",tab.pk());
					}
				}else {
					m.put("table",o.getClass().getSimpleName());
				}
			}
		}
		return m;
	}
	
	default Object update(Object o) {
		if(o instanceof Collection<?> c)return update(c);
		if(o.getClass().getName().endsWith(".ScriptObjectMirror")) {
			if(o.toString().equals("[object Array]"))return update(((Map)o).values());
		}
		var m=toMap(o);
		String pk=Lang.def(m.remove("+id"),"id").toString();
		String table=m.remove("table").toString();
		var where="WHERE "+pk+"=?";//默认更新条件
		var id=m.remove(pk);//取出id
		var column=new StringJoiner(",");
		m.forEach((k,v)->{
			column.add("`"+k+"`=?");
		});
		String sql="UPDATE %s SET %s %s".formatted(table,column,where);
		var args=new ArrayList(m.values());
		args.add(id);
		return executeUpdate(sql,args.toArray());
	}
	default int update(String table,Object o,Cnd cnd) {
		StringBuilder sb=new StringBuilder("update ");
		sb.append(table).append(" set ");
		var m=toMap(o);
		try {m.remove("table");m.remove("+id");}catch(UnsupportedOperationException e){}//忽略
		m.forEach((k,v)->{sb.append('`').append(k).append("` = ?,");});
		sb.deleteCharAt(sb.length()-1).append(cnd);
		cnd.insertParam(m.values());
		System.out.println(sb);
		System.out.println(cnd.getParams());
		return executeUpdate(sb.toString(),cnd.getParams().toArray());
	}
	
	default Object update(Collection<?> c){//批量更新
		return c;
	}

	default long count(String sql,Object...args) {return fun("count","*",sql,Long.class,args);}
	
	/**
	 * SQL函数计算
	 * @param fun AVG,COUNT,MAX,MIN,SUM
	 * @param name (表达式) 如*
	 * @param sql 表名 如包含sql则忽略
	 * @param t<T> 返回类型
	 * @param args sql参数 cnd则忽略其他参数
	 * @return
	 */
	default <T> T fun(String fun,String name,String sql,Class<T> t,Object...args) {
		if(!sql.trim().toLowerCase().startsWith("select ")) {
			sql="select "+fun+"("+name+") from "+sql;//sql 是表名
		}
		if(args!=null&&args[0] instanceof Cnd c) {
			c.notcount(false);//是统计对limit
			var o=queryForObject(t,sql+c.toString(),c.getParams().toArray());
			c.notcount(true);
			return o;
		}
		return queryForObject(t,sql,args);
	}
	/**
	 * TODO 批量更新 暂时未测试
	 * @param sql INSERT INTO test VALUES(?, ?, ?, ?, ?)
	 * @param args
	 * @return
	 */
	default int[] batch(String sql,List<Object[]> args) {
		var conn=getConnection();
		StringBuilder sb=new StringBuilder("batch sql:");
		try {
			conn.setAutoCommit(false);
			PreparedStatement stmt = conn.prepareStatement(sql);
			for (int i = 0; i < args.size(); i++) {
				Jdbc.setParam(stmt,args.get(i));stmt.addBatch();
				if(Jse.showsql&&stmt.getClass().getSimpleName().equals("ClientPreparedStatement")) {
					sb.append("\n").append(stmt.toString().substring(stmt.getClass().getName().length()+2));
				}
			}
			int[] cs = stmt.executeBatch();
			conn.commit();
			sb.append("\nbatch sql ok:").append(cs.length);
			if(Jse.showsql)Jdbc.log.debug(sb.toString());
			return cs;
		}catch(BatchUpdateException b) {
			int[] cs = b.getUpdateCounts();
			sb.append("\nbatch sql err:[");
			for (int i = 0; i < cs.length; i++) {
			  sb.append(cs[i]).append(",");
			}
			sb.deleteCharAt(sb.length()-1);
			Jdbc.log.warn(sb.append("] ").append(b.getMessage()).toString());
			try {conn.rollback();} catch (SQLException e1) {e1.printStackTrace();}//回滚
		}
		catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			Io.close(conn);
		}
		return null;
	}
	
	default int executeUpdate(String sql,Object...args) {
		try(var conn=getConnection();var stmt=conn.prepareStatement(sql);){
			if(sql.indexOf('?')!=-1)Jdbc.setParam(stmt,args);
			if(Jse.showsql&&stmt.getClass().getSimpleName().equals("ClientPreparedStatement")) {
				Jdbc.log.debug("executeUpdate"+stmt.toString().substring(stmt.getClass().getName().length()));
			}
			return stmt.executeUpdate();
		}catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
     * 判断表是否存在
     *
     * @param tableName  表名
     * @param dbType     数据库雷西看那个
     * @return boolean
     */
    default boolean exists(String tableName) {
        try {
            var rset = getConnection().getMetaData().getTables(null, null,tableName.toUpperCase(), null);
            return rset.next();
        } catch (SQLException e) {
        	return false;
        }
    }
    default int drop(String tableName) {
    	return executeUpdate("drop table "+tableName);
    }
    default void queryForStream(String sql){
    	var conn=getConnection();
    	try(var stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                java.sql.ResultSet.CONCUR_READ_ONLY);){
    		stmt.setFetchSize(Integer.MIN_VALUE);
    		var rs = stmt.executeQuery(sql);
    		
    		
		} catch (Exception e) {
			// TODO: handle exception
		}
    }
	
}
